<?php
/**
* @file $Id: TeacherCompletion.php 44 2006-07-08 19:37:23Z focus-sis $
* @package Focus/SIS
* @copyright Copyright (C) 2006 Andrew Schmadeke. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* Focus/SIS is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
*/

$QI = DBQuery("SELECT PERIOD_ID,TITLE,SHORT_NAME FROM SCHOOL_PERIODS WHERE SCHOOL_ID='".UserSchool()."' AND SYEAR='".UserSyear()."' ORDER BY SORT_ORDER ");
$periods_RET = DBGet($QI);

DrawHeader(ProgramTitle());

if(Preferences('NAME_FORMAT')=='Y')
	$full_name = "s.LAST_NAME||', '||s.FIRST_NAME||' '||COALESCE(s.MIDDLE_NAME,' ')";
else
	$full_name = "s.FIRST_NAME||' '||COALESCE(s.MIDDLE_NAME,' ')||' '||s.LAST_NAME";

$sql = "SELECT $full_name AS FULL_NAME,sp.TITLE,cp.PERIOD_ID,s.STAFF_ID,c.TITLE AS COURSE_TITLE,(select count(*) from schedule s where s.course_period_id=cp.course_period_id AND ('".DBDate()."' between s.start_date and s.end_date OR ('".DBDate()."'>=s.start_date AND s.end_date IS NULL))) AS STU_COUNT
		FROM USERS s,COURSE_PERIODS cp,COURSES c,SCHOOL_PERIODS sp 
		WHERE 
			sp.PERIOD_ID = cp.PERIOD_ID AND c.COURSE_ID=cp.COURSE_ID
			AND cp.TEACHER_ID=s.STAFF_ID AND cp.MARKING_PERIOD_ID IN (".GetAllMP('QTR',UserMP()).") 
			AND cp.SYEAR='".UserSyear()."' AND cp.SCHOOL_ID='".UserSchool()."' AND s.PROFILE='teacher'
		ORDER BY s.LAST_NAME,s.FIRST_NAME
		";

$RET = DBGet(DBQuery($sql),array(),array('STAFF_ID','PERIOD_ID'));

if(count($RET))
{
	foreach($RET as $staff_id=>$periods)
	{
		$i++;
		$staff_RET[$i]['FULL_NAME'] = $periods[key($periods)][1]['FULL_NAME'];
		foreach($periods as $period_id=>$period)
			$staff_RET[$i][$period_id] = $period[1]['STU_COUNT'].' - '.$period[1]['COURSE_TITLE'];
	}
}

$columns = array('FULL_NAME'=>_('Teacher'));
if(!$_REQUEST['period'])
{
	foreach($periods_RET as $period)
		$columns[$period['PERIOD_ID']] = $period['SHORT_NAME'].' '._('Students - Course');
}
ListOutput($staff_RET,$columns,_('Teacher'),_('Teachers'));

?>